﻿using System;
using System.Collections.Generic;
using System.Reflection;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Web;
using TidyBlog.Data.Mapping;

namespace TidyBlog.Data
{
    /// <summary>
    /// 数据库操作类
    /// </summary>
    public abstract class DbHelper
    {
        /// <summary>
        /// 当前数据库类型的DbProvider实例
        /// </summary>
        public static readonly DbProvider Provider;
        /// <summary>
        /// 数据库类型
        /// </summary>
        public static readonly string Type;


        static DbHelper()
        {
            Type = ConfigurationManager.AppSettings["DbType"];
            string wholeName = string.Format("TidyBlog.Data.{0}DbProvider, TidyBlog.Data", Type);
            try
            {
                Provider = (DbProvider)Activator.CreateInstance(System.Type.GetType(wholeName), true);
            }
            catch
            {
#if DEBUG
                throw;
#else
                throw new Exception("无法创建 " + wholeName + " ，请检查Web.config中的DbType节点配置");
#endif
            }
        }


        /// <summary>
        /// 获取或设置数据库请求次数
        /// </summary>
        public static byte QueryCount
        {
            get
            {
                byte queryCount = 0;
                if (HttpContext.Current != null)
                {
                    object temp = HttpContext.Current.Items["DbQueryCount"];
                    if (temp != null)
                    {
                        byte.TryParse(temp.ToString(), out queryCount);
                    }
                }

                return queryCount;
            }
            set
            {
                if (HttpContext.Current != null)
                {
                    HttpContext.Current.Items["DbQueryCount"] = value;
                }
            }
        }


        #region 创建数据库连接

        /// <summary>
        /// 使用默认的连接字符串创建数据库连接
        /// </summary>
        /// <returns>数据库连接</returns>
        public static IDbConnection CreateConnection()
        {
            return CreateConnection(Provider.ConnStr);
        }
        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <param name="connStr">连接字符串</param>
        /// <returns>数据库连接</returns>
        public static IDbConnection CreateConnection(string connStr)
        {
            IDbConnection conn = Provider.Factory.CreateConnection();
            conn.ConnectionString = connStr;
            return conn;
        }

        #endregion

        #region 附加命令参数

        /// <summary>
        /// 添加命令参数
        /// </summary>
        /// <param name="cmd">命令</param>
        /// <param name="cmdParams">命令参数</param>
        /// <returns>命令</returns>
        public static IDbCommand AttachParams(IDbCommand cmd, DbParameter[] cmdParams)
        {
            foreach (DbParameter p in cmdParams)
            {
                cmd.Parameters.Add(p);
            }
            return cmd;
        }
        /// <summary>
        /// 添加命令参数
        /// </summary>
        /// <param name="cmd">命令</param>
        /// <param name="cmdParam">命令参数</param>
        /// <returns>命令</returns>
        public static IDbCommand AttachParams(IDbCommand cmd, DbParameter cmdParam)
        {
            cmd.Parameters.Add(cmdParam);
            return cmd;
        }

        #endregion

        #region 创建命令

        /// <summary>
        /// 创建命令
        /// </summary>
        /// <returns>命令</returns>
        public static IDbCommand CreateCommand() { return Provider.Factory.CreateCommand(); }
        /// <summary>
        /// 创建命令
        /// </summary>
        /// <param name="cmdText">命令文本</param>
        /// <returns>命令</returns>
        public static IDbCommand CreateCommand(string cmdText)
        {
            IDbCommand cmd = CreateCommand();
            cmd.CommandText = cmdText;
            return cmd;
        }
        /// <summary>
        /// 创建命令
        /// </summary>
        /// <param name="cmdText">命令文本</param>
        /// <param name="cmdParam">命令参数</param>
        /// <returns>命令</returns>
        public static IDbCommand CreateCommand(string cmdText, DbParameter cmdParam)
        {
            return AttachParams(CreateCommand(cmdText), cmdParam);
        }
        /// <summary>
        /// 创建命令
        /// </summary>
        /// <param name="cmdText">命令文本</param>
        /// <param name="cmdParams">命令参数</param>
        /// <returns>命令</returns>
        public static IDbCommand CreateCommand(string cmdText, DbParameter[] cmdParams)
        {
            return AttachParams(CreateCommand(cmdText), cmdParams);
        }

        #endregion

        #region 创建参数

        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramValue">参数值</param>
        /// <param name="paramType">参数类型</param>
        /// <returns>参数</returns>
        public static DbParameter CreateParam(string paramName, object paramValue, DbType paramType)
        {
            DbParameter param = Provider.Factory.CreateParameter();
            param.ParameterName = paramName;
            param.DbType = paramType;
            param.Value = paramValue;
            return param;
        }
        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramValue">参数值</param>
        /// <param name="paramType">参数类型</param>
        /// <param name="paramSize">参数大小</param>
        /// <returns>参数</returns>
        public static DbParameter CreateParam(string paramName, object paramValue, DbType paramType, int paramSize)
        {
            DbParameter param = CreateParam(paramName, paramValue, paramType);
            if (paramSize > 0)
            {
                param.Size = paramSize;
            }
            return param;
        }

        #endregion

        #region 创建数字型参数

        // <summary>
        /// 创建byte类型参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramValue">参数值</param>
        /// <returns>参数</returns>
        public static DbParameter CreateParam(string paramName, byte paramValue)
        {
            return CreateParam(paramName, paramValue, DbType.Byte);
        }
        /// <summary>
        /// 创建short类型参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramValue">参数值</param>
        /// <returns>参数</returns>
        public static DbParameter CreateParam(string paramName, short paramValue)
        {
            return CreateParam(paramName, paramValue, DbType.Int16);
        }
        /// <summary>
        /// 创建ushort类型参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramValue">参数值</param>
        /// <returns>参数</returns>
        public static DbParameter CreateParam(string paramName, ushort paramValue)
        {
            return CreateParam(paramName, paramValue, DbType.UInt16);
        }
        /// <summary>
        /// 创建int类型参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramValue">参数值</param>
        /// <returns>参数</returns>
        public static DbParameter CreateParam(string paramName, int paramValue)
        {
            return CreateParam(paramName, paramValue, DbType.Int32);
        }
        /// <summary>
        /// 创建uint类型参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramValue">参数值</param>
        /// <returns>参数</returns>
        public static DbParameter CreateParam(string paramName, uint paramValue)
        {
            return CreateParam(paramName, paramValue, DbType.UInt32);
        }
        /// <summary>
        /// 创建Long类型参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramValue">参数值</param>
        /// <returns>参数</returns>
        public static DbParameter CreateParam(string paramName, long paramValue)
        {
            return CreateParam(paramName, paramValue, DbType.Int64);
        }
        /// <summary>
        /// 创建uint类型参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramValue">参数值</param>
        /// <returns>参数</returns>
        public static DbParameter CreateParam(string paramName, ulong paramValue)
        {
            return CreateParam(paramName, paramValue, DbType.UInt64);
        }
        /// <summary>
        /// 创建Float类型参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramValue">参数值</param>
        /// <returns>参数对象</returns>
        public static DbParameter CreateParam(string paramName, float paramValue)
        {
            return CreateParam(paramName, paramValue, DbType.Single);
        }
        /// <summary>
        /// 创建Decimal类型参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramValue">参数值</param>
        /// <returns>参数对象</returns>
        public static DbParameter CreateParam(string paramName, decimal paramValue)
        {
            return CreateParam(paramName, paramValue, DbType.Decimal);
        }
        /// <summary>
        /// 创建Double类型参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramValue">参数值</param>
        /// <returns>参数对象</returns>
        public static DbParameter CreateParam(string paramName, double paramValue)
        {
            return CreateParam(paramName, paramValue, DbType.Double);
        }

        #endregion

        #region 创建其他类型参数

        /// <summary>
        /// 创建布尔类型参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramValue">参数值</param>
        /// <returns>参数</returns>
        public static DbParameter CreateParam(string paramName, bool paramValue)
        {
            return CreateParam(paramName, paramValue, DbType.Boolean);
        }

        /// <summary>
        /// 创建日期类型参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramValue">参数值</param>
        /// <returns>参数对象</returns>
        public static DbParameter CreateParam(string paramName, DateTime paramValue)
        {
            return CreateParam(paramName, paramValue, DbType.DateTime);
        }

        #endregion

        #region 执行命令(ExecuteNonQuery)

        /// <summary>
        /// 执行数据库操作
        /// </summary>
        /// <param name="cmd">操作命令</param>
        /// <returns>影响的行数</returns>
        public static int ExecuteNonQuery(IDbCommand cmd)
        {
            return ExecuteNonQuery(cmd, CreateConnection());
        }
        /// <summary>
        /// 执行数据库操作
        /// </summary>
        /// <param name="cmd">操作命令</param>
        /// <param name="theConn">数据库连接</param>
        /// <returns>影响的行数</returns>
        public static int ExecuteNonQuery(IDbCommand cmd, IDbConnection theConn)
        {
            QueryCount++;
            using (IDbConnection conn = theConn)
            {
                conn.Open();
                cmd.Connection = conn;
                return cmd.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// 执行数据库操作
        /// </summary>
        /// <param name="cmd">操作命令</param>
        /// <param name="trans">事务</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(IDbCommand cmd, IDbTransaction trans)
        {
            if (trans == null)
            {
                return ExecuteNonQuery(cmd);
            }

            QueryCount++;
            cmd.Connection = trans.Connection;
            cmd.Transaction = trans;
            try
            {
                return cmd.ExecuteNonQuery();
            }
            catch
            {
                if (trans.Connection.State == ConnectionState.Open)
                {
                    trans.Connection.Close();
                }
                throw;
            }
        }

        #endregion

        #region 执行命令(ExecuteReader)

        /// <summary>
        /// 执行数据库操作命令
        /// </summary>
        /// <param name="cmd">操作命令</param>
        /// <returns>结果集</returns>
        public static IDataReader ExecuteReader(IDbCommand cmd)
        {
            return ExecuteReader(cmd, CreateConnection());
        }
        /// <summary>
        /// 执行数据库操作命令
        /// </summary>
        /// <param name="cmd">操作命令</param>
        /// <param name="conn">数据库连接</param>
        /// <returns>结果集</returns>
        public static IDataReader ExecuteReader(IDbCommand cmd, IDbConnection conn)
        {
            QueryCount++;
            cmd.Connection = conn;
            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                throw;
            }
        }

        #endregion

        #region 执行命令(ExecuteScalar)

        /// <summary>
        /// 执行数据库操作命令
        /// </summary>
        /// <param name="cmd">命令</param>
        /// <returns>结果集中第一行第一列的值</returns>
        public static object ExecuteScalar(IDbCommand cmd)
        {
            return ExecuteScalar(cmd, CreateConnection());
        }
        /// <summary>
        /// 执行数据库操作命令
        /// </summary>
        /// <param name="cmd">命令</param>
        /// <param name="theConn">数据库连接</param>
        /// <returns>结果集中第一行第一列的值</returns>
        public static object ExecuteScalar(IDbCommand cmd, IDbConnection theConn)
        {
            QueryCount++;
            using (IDbConnection conn = theConn)
            {
                conn.Open();
                cmd.Connection = conn;
                return cmd.ExecuteScalar();
            }
        }
        /// <summary>
        /// 执行数据库操作命令
        /// </summary>
        /// <param name="trans">使用的事务</param>
        /// <returns>结果集中第一行第一列的值</returns>
        public static object ExecuteScalar(IDbCommand cmd, IDbTransaction trans)
        {
            if (trans == null)
            {
                return ExecuteScalar(cmd);
            }

            QueryCount++;
            cmd.Connection = trans.Connection;
            cmd.Transaction = trans;
            try
            {
                return cmd.ExecuteScalar();
            }
            catch
            {
                if (trans.Connection.State == ConnectionState.Open)
                {
                    trans.Connection.Close();
                }
                throw;
            }
        }

        #endregion

        #region 执行命令(ExecuteDataTable)

        /// <summary>
        /// 执行数据库操作命令
        /// </summary>
        /// <param name="cmd">操作命令</param>
        /// <returns>转换为DataTable的结果集</returns>
        public static DataTable ExecuteDataTable(IDbCommand cmd)
        {
            return ExecuteDataTable(cmd, CreateConnection());
        }
        /// <summary>
        /// 执行数据库操作命令
        /// </summary>
        /// <param name="cmd">操作命令</param>
        /// <param name="theConn">数据库连接</param>
        /// <returns>转换为DataTable的结果集</returns>
        public static DataTable ExecuteDataTable(IDbCommand cmd, IDbConnection theConn)
        {
            QueryCount++;
            using (IDbConnection conn = theConn)
            {
                cmd.Connection = conn;
                conn.Open();
                IDbDataAdapter dataAdapter = Provider.Factory.CreateDataAdapter();
                dataAdapter.SelectCommand = cmd;
                DataSet dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                return dataSet.Tables[0];
            }
        }

        #endregion
    }
}
